package ar.edu.utn.frgp.banco.dao.Implements;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import ar.edu.utn.frgp.banco.dao.Interfaces.IntPrestamos;
import ar.edu.utn.frgp.banco.dao.connection.ImpConectionHandler;
import ar.edu.utn.frgp.banco.domain.Cliente;
import ar.edu.utn.frgp.banco.domain.PagoPrestamo;
import ar.edu.utn.frgp.banco.domain.Prestamos;
import ar.edu.utn.frgp.banco.exceptions.DataAccessException;

public class ImpPrestamos implements IntPrestamos {

	@Override
	public void AltaPrestamo(Prestamos prestamo) throws DataAccessException {
		PreparedStatement ps = null;
		ImpConectionHandler handler = new ImpConectionHandler();
		Connection cn = handler.getConnection();
		
		try {
			
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder.append("Insert into Prestamos (id_cuenta, id_cliente, fechaalta, ");
			stringBuilder.append("cantcuotas, monto, interes) ");
			stringBuilder.append("VALUES (?,?,?,?,?,?) ");
			
			ps = cn.prepareStatement(stringBuilder.toString());
			
			if(prestamo.getId_cuenta() == null){
				prestamo.setId_cuenta(0);
			}
			
			ps.setInt(1, prestamo.getId_cuenta());
			ps.setInt(2, prestamo.getId_cliente());
			ps.setDate(3, new java.sql.Date(prestamo.getFecha().getTime()));
			ps.setInt(4, prestamo.getCantidadCuotas());
			ps.setDouble(5, prestamo.getMonto());
			ps.setDouble(6, prestamo.getInteres());
			ps.executeUpdate();
			
		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			handler.closeResources(ps, cn);
		}

	}

	@Override
	public void ModiPrestamo(Prestamos prestamo) throws DataAccessException {
		PreparedStatement ps = null;
		ImpConectionHandler handler = new ImpConectionHandler();
		Connection cn = handler.getConnection();
		
		try {
			
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder.append("UPDATE Prestamos  set cantcuotas = ?, ");
			stringBuilder.append("monto = ?, interes = ? ");
			stringBuilder.append("WHERE id_prestamo = ? ");
			
			ps = cn.prepareStatement(stringBuilder.toString());
			
			ps.setInt(1, prestamo.getCantidadCuotas());
			ps.setDouble(2, prestamo.getMonto());
			ps.setDouble(3, prestamo.getInteres());
			ps.setInt(4, prestamo.getId_prestamo());
			ps.executeUpdate();
			
		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			handler.closeResources(ps, cn);
		}

	}

	@Override
	public Prestamos TraePrestamo(Integer id_prestamo)	throws DataAccessException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		ImpConectionHandler handler = new ImpConectionHandler();
		Connection cn = handler.getConnection();
		Prestamos pre = null;
		
		try {
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder.append("Select id_prestamo, id_cuenta, id_cliente, ");
			stringBuilder.append(" fechaalta, cantcuotas, monto, interes ");
			stringBuilder.append("from prestamos ");
			stringBuilder.append("WHERE id_prestamo = ?");

			ps = cn.prepareStatement(stringBuilder.toString());
			ps.setInt(1, id_prestamo);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				pre = new Prestamos();
				
				pre.setId_prestamo(rs.getInt("id_prestamo"));
				pre.setId_cuenta(rs.getInt("id_cuenta"));
				pre.setId_cliente(rs.getInt("id_cliente"));
				pre.setFecha(rs.getDate("fechaalta"));
				pre.setCantidadCuotas(rs.getInt("cantcuotas"));
				pre.setMonto(rs.getDouble("monto"));
				pre.setInteres(rs.getDouble("Interes"));
				pre.setMontocuota(rs.getDouble("monto")*rs.getDouble("Interes"));
				
			}
			
		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			handler.closeResources(rs, ps, cn);
		}
		return pre;
	}

	@Override
	public List<Prestamos> TraerPrestamos(Cliente cliente) throws DataAccessException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Prestamos> prestamos = new ArrayList<Prestamos>();
		
		ImpConectionHandler handler = new ImpConectionHandler();
		Connection cn = handler.getConnection();
		
		try {
				StringBuilder stringBuilder = new StringBuilder();
				stringBuilder.append("Select id_prestamo,id_cliente, id_cuenta,  ");
				stringBuilder.append("fechaalta, cantcuotas, monto, interes ");
				stringBuilder.append("from prestamos ");
				stringBuilder.append("WHERE id_cliente = ?");

				ps = cn.prepareStatement(stringBuilder.toString());
				ps.setInt(1, cliente.getID_Cliente());
				
				rs = ps.executeQuery();
				Prestamos pre = null;
				while (rs.next()) {
					pre = new Prestamos();
					
					pre.setId_prestamo(rs.getInt("id_prestamo"));
					pre.setId_cuenta(rs.getInt("id_cuenta"));
					pre.setId_cliente(rs.getInt("id_cliente"));
					pre.setFecha(rs.getDate("fechaalta"));
					pre.setCantidadCuotas(rs.getInt("cantcuotas"));
					pre.setMonto(rs.getDouble("monto"));
					pre.setInteres(rs.getDouble("Interes"));
					pre.setMontocuota(rs.getDouble("monto")*rs.getDouble("Interes"));
				
					prestamos.add(pre);
				}
		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			handler.closeResources(rs, ps, cn);
		}
		return prestamos;
	}

	public List<Prestamos> TraerPrestamosSinFinalizar(Cliente cliente) throws DataAccessException {
		
		List<Prestamos> resul = new ArrayList<Prestamos>();
		List<Prestamos> prestamos = TraerPrestamos(cliente);
		List<PagoPrestamo> pagoPres = null;
		
		ImpPagoPrestamo daoPres = new ImpPagoPrestamo();
		
		for (Prestamos pre : prestamos) {
			pagoPres = daoPres.TraePagoPrestamos(pre);
			
			//no tiene pagos registrados en ese prestamo
			if (pagoPres == null){
				resul.add(pre);
			}else{
			//tiene pagos
				double totalPagado = 0;
				for (PagoPrestamo pagoP : pagoPres) {
					totalPagado += pagoP.getMonto();
				}
				
				if (totalPagado < (pre.getMonto() * pre.getInteres())){
					resul.add(pre);
				}
			}
			
		}
		
		if (resul.size() == 0){
			return null;
		}else{
			return resul;
		}
		

	}
	
}
